﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Finisar.SQLite;

/*
 *  WILDCARDS
% (percent sign) represents zero, one, or more characters.

_ (underscore) represents exactly one character.

Wildcards are used with the LIKE keyword in SQL.

Below are some wildcard examples:

• 'A_Z': All string that starts with 'A', another character, and end with 'Z'. For example, 
 *      'ABZ' and 'A2Z' would both satisfy the condition, while 'AKKZ' would not 
 *      (because there are two characters between A and Z instead of one).

• 'ABC%': All strings that start with 'ABC'. For example, 'ABCD' and 'ABCABC' would 
 *      both satisfy the condition.

• '%XYZ': All strings that end with 'XYZ'. For example, 'WXYZ' and 'ZZXYZ' would 
 *      both satisfy the condition.

• '%AN%': All strings that contain the pattern 'AN' anywhere. For example, 'LOS ANGELES' 
 *      and 'SAN FRANCISCO' would both satisfy the condition.

• '_AN%': All strings that contain a character, then 'AN', followed by anything else. 
 *      For example, 'SAN FRANCISCO' would satisfy the condition, while 'LOS ANGELES' 
 *      would not satisfy the condition.

*/







namespace Daily_Log_Book
{

    public partial class frm_dailylog : Form
    {
        private SQLiteConnection sql_con;
        private SQLiteCommand sql_cmd;
        private SQLiteDataAdapter DB;
        private DataSet DS = new DataSet();
        private DataTable DT = new DataTable();
        //private DataGrid Grid;
        private TimeSpan calltimebegin;
        private TimeSpan calltimeend;
        //private DateTime timeofday = new DateTime();

        private bool add_success = false;



        public frm_dailylog()
        {
            InitializeComponent();
            btn_callEnd.Enabled = false;


            //disable search criteria 2 until checkbox is checked
            tb_search2.Enabled = false;
            comboBox_criteria2.Enabled = false;
            cb_enablecriteria2.Checked = false;
        }

        private void frm_dailylog_Load(object sender, EventArgs e)
        {
            
        }

        private void btn_addEntry_Click(object sender, EventArgs e)
        {
            //timeofday = System.DateTime.Now.TimeOfDay();
            /*
            DateTime dateTime = Convert.ToDateTime(System.DateTime.Now.TimeOfDay);
            dateTime = new DateTime(
                dateTime.Ticks - (dateTime.Ticks % TimeSpan.TicksPerSecond),
                dateTime.Kind
                );
            */
            TimeSpan dateTime = System.DateTime.Now.TimeOfDay;
            dateTime = new TimeSpan(
                dateTime.Ticks - (dateTime.Ticks % TimeSpan.TicksPerSecond)
                //dateTime.Kind
                );

            try
            {
                if (tb_addModel.Text == null) tb_addModel.Text = " ";
                if (tb_addPhone.Text == null) tb_addPhone.Text = " ";
                if (tb_addID.Text == null) tb_addID.Text = " ";
                if (tb_addInvoicenum.Text == null) tb_addInvoicenum.Text = " ";
                if (tb_addName.Text == null) tb_addName.Text = " ";
                if (tb_addNotes.Text == null) tb_addNotes.Text = " ";
                if (tb_addPL.Text == null) tb_addPL.Text = " ";
                if (tb_addVIN.Text == null) tb_addVIN.Text = " ";
                if (lbl_callTime.Text == "Call in progress") end_call();
                //if (tb= null) tb_addModel.Text = " ";

                //Add();
                string txtSQLQuery = "insert into  main (phone, customer, vin, model, invoice, salesperson, notes, custid, pl, callduration, date, time) " +
                     "values ('" +
                     tb_addPhone.Text + "', '" +
                     tb_addName.Text + "', '" +
                     tb_addVIN.Text + "', '" +
                     tb_addModel.Text + "', '" +
                     tb_addInvoicenum.Text + "', '" +
                     combobox_salesperson.Text + "', '" +
                     tb_addNotes.Text + "', '" +
                     tb_addID.Text + "', '" +
                     tb_addPL.Text + "', '" +
                     lbl_callTime.Text + "', '" +
                    //Convert.ToString(dateTimePicker_add.Value) + ", " +
                     Convert.ToString(System.DateTime.Now.ToShortDateString()) + "', '" +
                     Convert.ToString(dateTime) + "')";
                     //Convert.ToString(System.DateTime.Now.TimeOfDay) + "')";
                ExecuteQuery(txtSQLQuery);

                add_success = true;
            }
            catch (Exception ex)
            {
                add_success = false;
                MessageBox.Show(ex.Message, "Error!");
            }
            finally
            {
                if (add_success == true)
                {
                    MessageBox.Show("Entry added successfully!");
                    clear_entries();
                }
                add_success = false;
            }

            
        }

        private void SetConnection()
        {
            sql_con = new SQLiteConnection
                ("Data Source=customerLog;Version=3;New=False;Compress=True;");
        }

        private void ExecuteQuery(string txtQuery)
        {
            //try
            //{
                SetConnection();
                sql_con.Open();
                sql_cmd = sql_con.CreateCommand();
                sql_cmd.CommandText = txtQuery;
                sql_cmd.ExecuteNonQuery();
                sql_con.Close();
            //}
            //catch { }
        }

        private void LoadData()
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            //string CommandText = "select id and vin and notes from main";
            string CommandText = "select * from main";
            DB = new SQLiteDataAdapter(CommandText, sql_con);
            DS.Reset();
            DB.Fill(DS);
            DT = DS.Tables[0];
            dataGridView1.DataSource = DT;
            sql_con.Close();
        }

        private void LoadData(string command)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            //string CommandText = "select id and vin and notes from main";
            //string CommandText = "select * from main";
            string CommandText = command;
            DB = new SQLiteDataAdapter(CommandText, sql_con);
            DS.Reset();
            DB.Fill(DS);
            DT = DS.Tables[0];
            dataGridView1.DataSource = DT;
            sql_con.Close();
        }
        private void LoadData(string command, DataGridView grid)
        {
            SetConnection();
            sql_con.Open();
            sql_cmd = sql_con.CreateCommand();
            //string CommandText = "select id and vin and notes from main";
            //string CommandText = "select * from main";
            string CommandText = command;
            DB = new SQLiteDataAdapter(CommandText, sql_con);
            DS.Reset();
            DB.Fill(DS);
            DT = DS.Tables[0];
            //dataGridView1.DataSource = DT;
            grid.DataSource = DT;
            sql_con.Close();
        }

        private void btn_searchAll_Click(object sender, EventArgs e)
        {
            //LoadData();      
            LoadData("select * from main");
        }

          private void Add()
          {  
             string txtSQLQuery = "insert into  main (notes) values ('"+tb_addNotes.Text+"')";
             ExecuteQuery(txtSQLQuery);
          }

          private void btn_debugcmd_Click(object sender, EventArgs e)
          {
              try
              {
                  /*
                  SetConnection();
                  sql_con.Open();
                  sql_cmd = sql_con.CreateCommand();
                  //string CommandText = "select id and vin and notes from main";
                  string CommandText = Convert.ToString(tb_debugcmd.Text);
                  DB = new SQLiteDataAdapter(CommandText, sql_con);
                  DS.Reset();
                  DB.Fill(DS);
                  DT = DS.Tables[0];
                  dataGridView2.DataSource = DT;
                  sql_con.Close();
                  */
                  LoadData(tb_debugcmd.Text, dataGridView2);
              }
              catch (Exception ex)
              {
                  //MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                  tb_debugerror.Text = ex.Message;
              }
          }

          private void btn_today_Click(object sender, EventArgs e)
          {
              dateTimePicker_add.Value = DateTime.Today;
          }

          private void btn_callBegin_Click(object sender, EventArgs e)
          {
              btn_callBegin.Enabled = false;
              calltimebegin = System.DateTime.Now.TimeOfDay;
              btn_callEnd.Enabled = true;
              lbl_callTime.Text = "Call in progress";
              
          }

          private void btn_callEnd_Click(object sender, EventArgs e)
          {
              end_call();
          }


          private TimeSpan time_difference(TimeSpan beginning, TimeSpan end)
          {
              TimeSpan duration = new TimeSpan();

              duration = end - beginning;              

              btn_callBegin.Enabled = true;

              return duration;                        
          }

          private void end_call()
          {
              btn_callBegin.Enabled = true;
              btn_callEnd.Enabled = false;

              calltimeend = System.DateTime.Now.TimeOfDay;

              lbl_callTime.Text = Convert.ToString(time_difference(calltimebegin, calltimeend));

              calltimeend = TimeSpan.Zero;
              calltimebegin = TimeSpan.Zero;
          }

          private void exitToolStripMenuItem_Click(object sender, EventArgs e)
          {
              // Display a message box asking users if they
              // want to exit the application.
              if (check_for_entries() == true)
              {
                  if (MessageBox.Show("You have unsaved data, exit anyway?", "My Application",
                        MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                        == DialogResult.Yes)
                  {
                      Application.Exit();
                  }
              }
              else Application.Exit();
              //Application.Exit();
          }

          private void btn_clear_Click(object sender, EventArgs e)
          {
              clear_entries();
          }

          private void clear_entries()
          {
              if (lbl_callTime.Text == "Call in progress") end_call();

              tb_addName.Text = "";
              tb_addPhone.Text = "";
              tb_addVIN.Text = "";
              tb_addModel.Text = "";
              tb_addInvoicenum.Text = "";
              tb_addNotes.Text = "";
              tb_addID.Text = "";
              tb_addPL.Text = "";
              lbl_callTime.Text = "";


          }

          private bool check_for_entries()
          {
              bool filled_entry = false;

              if (!(tb_addName.Text == "")) filled_entry |= true;
              if (!(tb_addPhone.Text == "")) filled_entry |= true;
              if (!(tb_addVIN.Text == "")) filled_entry |= true;
              if (!(tb_addInvoicenum.Text == "")) filled_entry |= true;
              if (!(tb_addNotes.Text == "")) filled_entry |= true;
              if (!(tb_addID.Text == "")) filled_entry |= true;
              if (!(tb_addPL.Text == "")) filled_entry |= true;
              //if (!(lbl_callTime.Text == "")) filled_entry |= true;
            
              if (filled_entry == true) return true;
              else return false;
          }

          private void get_max_entry()
          {

          }

          private void aboutToolStripMenuItem_Click(object sender, EventArgs e)
          {

          }

          private void btn_delete_Click(object sender, EventArgs e)
          {
              //if (btn_delete.Text == "") ;

          }

          private void btn_search_Click(object sender, EventArgs e)
          {
              //search through database using given criteria.
              /*
              SELECT AverageTemperature 
                FROM Weather
                WHERE City = 'New York'
               * 
               * SELECT * 
                FROM Weather
                WHERE AverageTemperature > = 20 AND City <> 'New York'
               */
              string search_command = "";

              switch (comboBox_criteria1.Text)
              {
                  case "Date":
                  {
                      //search_command = "select * from main where date = " + 
                      break;
                  }
                  case "Phone No.": 
                      {
                          search_command = "select * from main where phone = "+tb_search1.Text;
                          if (cb_searchwithdate.Checked == true) search_command += " AND date = " + dateTimePicker_search.Value.ToShortDateString();
                          break;
                      }
                  case "Name":
                      {
                          search_command = "select * from main where customer = " + tb_search1.Text;
                          break;
                      }
                  case "VIN":
                      {
                          search_command = "select * from main where vin = " + tb_search1.Text;
                          break;
                      }
                  case "Salesperson": break;
                  default:
                      break;
              }


              //string search_command = "select * from main where "+ 

              LoadData(search_command, dataGridView1);

              


              /*
              switch (cb_enablecriteria2.CheckState)
              {
                  case CheckState.Checked:
                      {
                          tb_search2.Enabled = true;
                          comboBox_criteria2.Enabled = true;
                          break;
                      }

                  case CheckState.Indeterminate:
                      {
                          break;
                      }
                  case CheckState.Unchecked:
                      {
                          tb_search2.Enabled = false;
                          comboBox_criteria2.Enabled = false;
                          break;
                      }
                  default:
                      {
                          break;
                      }
              }*/

          }

          private void cb_enablecriteria2_CheckedChanged(object sender, EventArgs e)
          {
              switch (cb_enablecriteria2.CheckState)
              {
                  case CheckState.Checked:
                      {
                          tb_search2.Enabled = true;
                          comboBox_criteria2.Enabled = true;
                          break;
                      }

                  case CheckState.Indeterminate:
                      {
                          break;
                      }
                  case CheckState.Unchecked:
                      {
                          tb_search2.Enabled = false;
                          comboBox_criteria2.Enabled = false;
                          break;
                      }
                  default:
                      {
                          break;
                      }
              }

          }

          private void comboBox_criteria1_MouseClick(object sender, MouseEventArgs e)
          {
              comboBox_criteria1.DroppedDown = true;
          }

          private void comboBox_criteria2_MouseClick(object sender, MouseEventArgs e)
          {
              comboBox_criteria2.DroppedDown = true;
          }


        }


                     
}
